DATA DRIVEN SECURITY

import os
import glob
import pandas
import geoip2.database as db
import datetime
from plotnine import *
import matplotlib.pyplot as plt
import plotly as py
import plotly.express as px
import plotly.graph_objects as go

pandas.options.display.max_columns = 10

def getdataframe(source,dest = ""):
    files = glob.glob(os.path.join(source,"*.ipset"))
    dftotal = pandas.DataFrame(columns=["IP","Category","Maintainer","Country","Date"])
    reader = db.Reader(r"./GeoLite2-Country_20191210/GeoLite2-Country.mmdb")
    for f in files:
        ips = []
        countrylist = []
        cat = ""
        main = ""
        date = ""
        df = pandas.DataFrame(columns=["IP","Category","Maintainer","Country","Date"])
        aux = open(f,"r")
        content = aux.readlines()
        aux.close()
        for l in  content:
            if "Category" in l:
                cat = str(l.split(":")[-1]).strip()
            elif "Maintainer" in l:
                main = str(l.split(":")[-1]).strip().replace("/","")
            elif "#" not in l:
                ips.append(str(l).strip())
            elif "This File Date" in l:
                date = str(l.split(" : ")[-1]).strip().replace("  "," ")
                try:
                    date = str(datetime.datetime.strptime(date, "%a %b %d %H:%M:%S UTC %Y"))
                except:
                    print(str(l))
        df["IP"] = ips
        df["Category"] = [cat]*len(ips)
        df["Maintainer"] = [main]*len(ips)
        df["Date"] = [date]*len(ips)
        for ip in df.IP.values:
            try:
                c = reader.country(ip).country.names["en"]
            except:
                c = ''
            countrylist.append(c)
        df["Country"] = countrylist
        dftotal = dftotal.append(df, ignore_index = True)
    if dest != "" and not os.path.exists(dest):
        dftotal.to_csv(dest,index=False)
    return dftotal

def dropduplicates(dataframe,groupcolumns):
    aux = dataframe.copy()
    aux["Occurrence"] = df.groupby(groupcolumns).cumcount() + 1
    return aux.drop_duplicates(subset = groupcolumns, keep = "last")
    
def correlation(dataframe1,dataframe2):
    normalized = []
    country = []
    for c in dataframe2.columns[2:]:
        try:
            a = int(dataframe2[c])
            b = int(dataframe1.loc[dataframe1["Country Name"] == c]["2018"])
            normalized.append((a/b)*1000)
            country.append(c)
        except:
            pass
    aux = pandas.DataFrame(columns=["Country","Risk"])
    aux["Country"] = country
    aux["Risk"] = normalized
    return aux
    
def correlation2(dataframe):
    it = dataframe.iterrows()
    try:
        next(it)
        country = []
        category = []
        for i in range(len(dataframe.index)):
            aux = next(it)
            country.append(aux[0])
            a1 = aux[1].keys().to_list()
            a2 = list(aux[1].values)
            c = a1[a2.index(max(a2))]
            category.append(c)
    except:
        pass
    dfret = pandas.DataFrame(columns=["Country","Category"])
    dfret["Country"] = country
    dfret["Category"] = category
    return dfret

def correlation3(df3,df4,df6,df7):
    #Es creen llistes buides que anem omplint amb les dades que ens interesen
    normalized = []
    country = []
    code = []
    category = []
    category2 = []
    for c in df4.columns[2:]:
        try:
            a = int(df4[c])
            b = int(df3.loc[df3["Country Name"] == c]["2018"])
            normalized.append((a/b)*1000)
            country.append(c)
            #Es fa una cerca per trobar el país amb el que coincideix
            #i treure l'iterador per introduir el codi correcte
            s = -1
            for cou in df3.iloc[0:,0]:
                s = s+1
                if cou == c:
                    code.append(df3.at[s, 'Country Code'])
            it = -1
            for i in df6.iloc[0:,0]:
                it = it+1
                if i == c:
                    category.append(df6.at[it, 'Category'])
            it2 = 0
            t = False
            for i2 in df7.iloc[0:,0]:
                if i2 == c:
                    category2.append(df7.at[it2, 'Category'])
                    t = True
                it2 = it2+1
                if it2 == 225:
                    if t == False:
                        category2.append("No data")
                    t = False
        except:
            pass
    aux = pandas.DataFrame(columns=["Country","Risk", "Code", "Category","No Abuse"])
    aux["Country"] = country
    aux["Risk"] = normalized
    aux["Code"] = code
    aux["Category"] = category
    aux["No Abuse"] = category2
    return aux

#df = getdataframe(r".\blocklist-ipsets-master")
df = pandas.read_csv(r".\brutedataframe_country_date.csv")
df

df1 = pandas.read_csv(r".\API_SP.POP.TOTL_DS2_en_csv_v2_566132.csv")
df1

df2 = dropduplicates(df,["IP","Category"])
df2

df3 = df2.pivot(index = "IP", columns = "Category", values = "Occurrence")
df3

a = df3.agg("sum")
a

Category
abuse            1383986.0
anonymizers       133325.0
attacks           538736.0
malware           185034.0
organizations      91017.0
reputation        236887.0
spam              204383.0
dtype: float64

ggplot(df, aes(x="Category")) + geom_bar(stat = 'count')

<ggplot: (97737331250)>

def donutplot(a):
        fig = plt.figure()
        fig.patch.set_facecolor("white")
        plt.rcParams["text.color"] = "black"
        my_circle = plt.Circle((0,0), 0.7, color="white")
        plt.pie(a.values,labels=df3.columns)
        p = plt.gcf()
        p.gca().add_artist(my_circle)
        return p

p = donutplot(a)

df4 = dropduplicates(df,["IP"])
df4 = df4.pivot(index = "IP", columns = "Country", values = "Occurrence")
df4 = df4.agg(["sum"])
df4

df5 = correlation(df1,df4)
df5

dfcat = df.copy()
df6 = dropduplicates(dfcat,["Category","Country"])
df6 = df6.pivot(index = "Country", columns = "Category", values = "Occurrence")
df6 = correlation2(df6)
df6

dfcat = df.copy()
dfcat = df[df.Category != "abuse"]
df7 = dropduplicates(dfcat,["Category","Country"])
df7 = df7.pivot(index = "Country", columns = "Category", values = "Occurrence")
df7 = correlation2(df7)
df7

def rewrite(dataframe):
        d = {'Venezuela, RB':'Venezuela',"Egypt, Arab Rep.":"Egypt","Korea, Rep.":"South Korea","Iran, Islamic Rep.":"Iran","Yemen, Rep.":"Yemen",
        "Czech Republic":"Czechia","Syrian Arab Republic":"Syria","Congo, Rep.":"Congo Republic",
        "Slovak Republic":"Slovakia","Lithuania":"Republic of Lithuania","Moldova":"Republic of Moldova",
        "Kyrgyz Republic":"Kyrgyzstan","Lao PDR":"Laos","Jordan":"Hashemite Kingdom of Jordan","Congo, Dem. Rep.":"DR Congo",
        "Cote d'Ivoire":"Ivory Coast","Seychelles":"Sey","British Virgin Islands":"BR","Russian Federation":"Russia"}
        dataframe = dataframe.replace(d)
        dataframe.drop(dataframe.columns[len(dataframe.columns)-1],axis=1,inplace=True)
        return dataframe

df1rw = rewrite(df1)
df8 = correlation3(df1rw,df4,df6,df7)
df8

def map():
        dfmap = df8.copy()
        dfmap['text'] = dfmap['Country'] + '<br>' +         dfmap['Code'] + '<br>' +         'Main category: '  + dfmap['Category'] + '<br>' +         'Category withouth abuse: ' + dfmap['No Abuse']
        fig = go.Figure(data=go.Choropleth(
            locations = dfmap['Code'],
            z = dfmap['Risk'],
            text = dfmap['text'],
            colorscale = 'Reds',
            autocolorscale=False,
            reversescale=False,
            marker_line_color='darkgray',
            marker_line_width=0.5,
            colorbar_tickprefix = '',
            colorbar_title = 'number of risk',
        ))
        fig.update_layout(
            title_text='Daily Global Risk',
            geo=dict(
                showframe=False,
                showcoastlines=False,
                projection_type='equirectangular'
            ),
            annotations = [dict(
                x=0.55,
                y=0.1,
                xref='paper',
                yref='paper',
                text='Source: Data Driven Class',
                showarrow = False
            )]
        )
        py.offline.plot(fig,filename="mapa.html")

map()

df = pandas.read_csv(r".\df2month.csv", encoding = "ISO-8859-1")
df

def days_repited(df):
  #Para sacar dias en total que una IP que se ha tenido en activo en dos meses
  count_series = df.groupby(['ioc']).size()
  return count_series.to_frame(name = 'Dias').reset_index()

df9 = days_repited(df)
df9

def country(df1,dest = ""):
  # Sacamos el pais de cada IP maliciosa
  df = pandas.DataFrame(columns=["IP","Days","Country"])
  dftotal = pandas.DataFrame(columns=["IP","Days","Country"])
  reader = db.Reader(r"./GeoLite2-Country_20191210/GeoLite2-Country.mmdb")
  ips = []
  countrylist = []
  days = []
  s = 0
  for ip in df1.iloc[0:,0]:
    try:
      c = reader.country(ip).country.names["en"]
      countrylist.append(c)
      ips.append(df1.at[s, 'ioc'])
      days.append(df1.at[s, 'Dias'].astype(int))
    except:
      #c = ''
      pass
    s = s +1
  df["IP"] = ips
  df["days"] = days
  df["Country"] = countrylist
  dftotal = dftotal.append(df, ignore_index = True)
  if dest != "" and not os.path.exists(dest):
    dftotal.to_csv(dest,index=False)
  return dftotal

def agg_country(df):
  # Agrupamos en total cuantos dias acumula cada pais de IPs maliciosas. Para saber si
  # ese pais por ejemplo elimina rapido sus IPs reportadas como maliciosas
  count_series = df.groupby(['Country']).size()
  new_df = count_series.to_frame(name = 'Dias totales').reset_index()
  return new_df

#df10 = country(df9,dest= r"./IP_Days_total.csv")
df10 = pandas.read_csv(r"./IP_Days_total.csv")
df10 = agg_country(df10)
df10

def code(df,df5):
  # Para meter el codigo ya que sino el mapa no se genera.
  s = -1
  country = []
  days = []
  code = []
  s1 = -1
  for c in df.iloc[0:,0]:
    s = -1
    s1 = s1 +1
    try:
      for cou in df5.iloc[0:,0]:
        s = s+1
        if cou == c:
          #print (df5.at[s, 'Country Code'],df5.at[s, 'Country Name'])
          code.append(df5.at[s, 'Country Code'])
          country.append(df5.at[s, 'Country Name'])
          days.append(df.at[s1, 'Dias totales'])
    except:
      pass
  aux = pandas.DataFrame(columns=["Country","Dias totales", "Code"])
  aux["Country"] = country
  aux["Dias totales"] = days
  aux["Code"] = code
  #El dataframe final usado para el mapa es este
  return aux

df11 = code(df10,df1rw)
df11

def map2():
        dfmap = df11.copy()
        dfmap['text'] = dfmap['Country'] + '<br>' +         dfmap['Code']
        fig2 = go.Figure(data=go.Choropleth(
            locations = dfmap['Code'],
            z = dfmap['Dias totales'],
            text = dfmap['text'],
            colorscale = 'Blues',
            autocolorscale=False,
            reversescale=False,
            marker_line_color='darkgray',
            marker_line_width=0.5,
            colorbar_tickprefix = '',
            colorbar_title = 'Dias totales',
        ))
        fig2.update_layout(
            title_text='Total de dias que han estado las IPs reportadas como maliciosas activas en dos meses',
            geo=dict(
                showframe=False,
                showcoastlines=False,
                projection_type='equirectangular'
            ),
            annotations = [dict(
                x=0.55,
                y=0.1,
                xref='paper',
                yref='paper',
                text='Source: Data Driven Class',
                showarrow = False
            )]
        )
        py.offline.plot(fig2,filename="mapa22.html")

map2()